<?php
/**
 * User: Hunman
 * Date: 20.07.13
 * Time: 13:21
 */

class Model_Search extends Model_Model
{
    public
    function getSearchData($query)
    {
        $query = preg_replace('/[^0-9A-Za-zА-Яа-яЁёіІїЇ-]/u', " ", $query);
        $queryArray = explode(" ", $query);
        $advancedSearch = "";

        foreach ($queryArray as $queryElements) {
            if (strlen($queryElements) > 3)
                $advancedSearch .= <<<SQL
                      OR
                      t.Name LIKE  "%$queryElements%" OR
                      t.Data LIKE "%$queryElements%" OR
                      pd.podcat LIKE "%$queryElements%" OR
                      cd.cat LIKE "%$queryElements%" OR
                      t.mini_data LIKE "%$queryElements%"
SQL;
        }

        $sql = <<<SQL
                   SELECT
                      t.id, t.Name, t.Image, t.Price, t.PriceOld, t.Data, t.mini_data,
                      tc.podcat AS podcat_id, tc.cat AS cat_id, pd.podcat AS podcat_name, cd.cat AS cat_name
                   FROM
                      Tovar AS t
                   LEFT JOIN
                      Tovar_cat AS tc ON tc.id_tovar = t.id
                   RIGHT JOIN
                      podcat_data AS pd ON pd.id = tc.podcat
                   RIGHT JOIN
                      cat_data AS cd ON cd.id = tc.cat
                   WHERE
                      t.Name LIKE  "%$query%" OR
                      t.Data LIKE "%$query%" OR
                      pd.podcat LIKE "%$query%" OR
                      cd.cat LIKE "%$query%" OR
                      t.mini_data LIKE "%$query%"
                      $advancedSearch
				   AND
					  t.in_moderate = 0
				   AND
					  t.is_hide = 0

SQL;
        $db = $this->dbh();
        $stmt = $db->query($sql);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }
}